package com.ivo.quartz;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.client.RestTemplate;

import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonParser;
import com.ivo.biz.PerformanceDay;
import com.ivo.biz.PerformanceDayManager;
import com.ivo.biz.PerformanceMon;
import com.ivo.core.hibernate.HibernateInvoker;
import com.ivo.sys.Code;
import com.ivo.util.DateUtil;
import com.ivo.util.StringUtil;

@Service
@Repository
public class QualityQuartz {

	protected Logger logger = LoggerFactory.getLogger(this.getClass());

	@Autowired
	private PerformanceDayManager performanceDayManager;
	
	@Autowired
	private HibernateInvoker hibernateInvoker;
	
	@Resource(name = "dsEdw")
	private DataSource dsEdwDataSource;
	
	@Resource(name = "dataSource")
	private DataSource pmsDataSource;
	
	@Resource(name = "scmdataSource")
	private DataSource scmDataSource;

	
	@Transactional
	@Scheduled(cron = "0 0 8 16 * ? ")
	public void execute(){
		IPQC();
		MO();
		SCRAP();
	}
	@Transactional
	@Scheduled(cron = "0 10 8 * * ? ")
	public void execute1(){
		DPPM();
	}
	/*
	 * 同步IPQC数据
	 */
	@Transactional
	public void IPQC(){
		String fab = "";
		String Fab_date = "";
		String employee_id = "";
		String reason = "";
		String employee_name = "";
		RestTemplate restTemplate = new RestTemplate();
		JsonParser parser = new JsonParser();
		logger.info("同步IPQC数据start");
	    String url = "http://myivo.ivo.com.cn/activitiClient/rest/getPirList.do?fab=&defect=&defectLevel=&draftDate=";
	    String json = restTemplate.getForObject(url, String.class);  
	    JsonArray Jarray = parser.parse(json).getAsJsonArray();
	    for(int i =0; i<Jarray.size();i++){
	    	fab = Jarray.get(i).getAsJsonObject().get("Fab").getAsString();
	    	Fab_date = Jarray.get(i).getAsJsonObject().get("AuditDate").getAsString();
	    	employee_id = Jarray.get(i).getAsJsonObject().get("AuditedPerson_FK").getAsString();
	    	reason = Jarray.get(i).getAsJsonObject().get("AbnormalDesic").getAsString();
	    	employee_name = Jarray.get(i).getAsJsonObject().get("AuditedPerson").getAsString();
	    	Matcher matcher=Pattern.compile("[a-zA-Z]").matcher(employee_name);
	    	if(matcher.find()){  
	    		employee_name = employee_name.substring(0, matcher.start()-1);  
	    	 }else{
	    		 
	    	 }
	    	
	    

	    	
	    	Date fab_date = new Date(Long.parseLong(Fab_date));
	    	
	    	PerformanceDay pd = new PerformanceDay();
	    	PerformanceMon pm = new PerformanceMon();
	    	if(fab.equals("LCM1")){
	    		pd.setFactory("M1");
	    		}else if(fab.equals("LCM2")){
	    			pd.setFactory("M2");
	    		}else if(fab.equals("ARY")){
	    			pd.setFactory("ARY");
	    		}
	    	if(StringUtil.isNotEmpty(employee_id) && !employee_id.equals("/")){
	    		pm = hibernateInvoker.findUnique(PerformanceMon.class, "from PerformanceMon p where p.emp_id = '" + employee_id + "' and p.month = '" + DateUtil.formatDate(new Date()).substring(0, 7) +"'");
	    		if(pm != null){
	    			pd.setOpe_fk(pm.getOpe_fk());
	    		}else{
	    			continue;
	    		}
	    	}
	    	if(employee_name.contains(";")){
	    		pd.setEmp_name(employee_name.split(";")[0]);
	    	}else{
	    		pd.setEmp_name(employee_name);
	    	}
	    	
	    	pd.setEmp_id(employee_id);
	    	pd.setFab_date(fab_date);
	    	pd.setCategory("IPQC");
	    	pd.setReason(reason);
	    	pd.setType(31L);
	    	performanceDayManager.save(pd);
	    }
	    logger.info("同步IPQC数据end");
	}
	
	
	/*
	 * 同步MO数据
	 */
	@Transactional
	public void MO(){
		logger.info("同步MO数据start");
		RestTemplate restTemplate = new RestTemplate();
		JsonParser parser = new JsonParser();
		String url = "http://myivo.ivo.com.cn/FHR/restful/getMoForPEByMon.do";
		String json = restTemplate.getForObject(url, String.class); 
		String EMP_ID = "";
		String FAB_DATE = "";
		String OPE_FK = "";
		String REASON = "";
		String EMP_NAME = "";
		String FACTORY = "";
		JsonArray Jarray   = parser.parse(json).getAsJsonArray();
		   for(int i =0; i<Jarray.size();i++){
			   	EMP_ID = Jarray.get(i).getAsJsonObject().get("employee_id").getAsString();
		    	FAB_DATE = Jarray.get(i).getAsJsonObject().get("dateOfOccur").getAsString();
		    	OPE_FK = Jarray.get(i).getAsJsonObject().get("operation_id").getAsString();
		    	REASON = Jarray.get(i).getAsJsonObject().get("reason").getAsString();
		    	EMP_NAME = Jarray.get(i).getAsJsonObject().get("employee_name").getAsString();
		    	FACTORY = Jarray.get(i).getAsJsonObject().get("scope").getAsString();
		    	PerformanceDay p = new PerformanceDay();
		    	p.setEmp_id(EMP_ID);
		    	p.setFab_date(DateUtil.parseDate(FAB_DATE));
		    	Code co = hibernateInvoker.findUnique(Code.class, "from Code co where co.name = '" + OPE_FK + "'");
		    	if(co.getName().equals(OPE_FK)){
		    		p.setOpe_fk(co.getId());
		    	}
		    	p.setReason(REASON);
		    	p.setEmp_name(EMP_NAME);
		    	p.setType(32L);
		    	p.setFactory(FACTORY);
		    	p.setCategory("MO");
		    	performanceDayManager.save(p);
		    }
		   logger.info("同步MO数据end");	
	}
		
	/*
	 * 同步破片数据
	 */
	@Transactional
	public void SCRAP(){
		logger.info("同步破片数据start");
		logger.info("同步破片数据end");
	}
	
	/*
	 * 同步DPPM数据
	 */
	@Transactional
	public void DPPM(){
		logger.info("同步DPPM数据start");
		List<String[]> list = new <String[]>ArrayList();
		Calendar cal = Calendar.getInstance();
		cal.add(Calendar.DATE, -1);
		String dateOfThis1 = DateUtil.formatDate(cal.getTime());
		String dateOfThis = DateUtil.formatDate(new Date()).substring(0, 10);
		JdbcTemplate JdbcTemplate = new JdbcTemplate(dsEdwDataSource);
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(*),F.EVT_USER FROM (select NVL(SUM(DECODE(SUBSTR(RSN_CODE,1,1),'Q',1,0)),0) ");
        sql.append("  N_QTY,FAB_KEY,PROD_KEY,WO_KEY,PNL_ID,EVT_TIME from SOR.WPP_MMOVEMENT where  EVT_TYPE ='OQCF' ");
        sql.append("  AND SUBSTR(WO_ID,3,1) IN ('1','8','5','A')GROUP BY FAB_KEY,PROD_KEY,WO_KEY,PNL_ID,EVT_TIME ");
        sql.append(" )E INNER JOIN ( SELECT PNL_ID,FAB_KEY,EVT_TIME,EVT_USER,FAB_DATE FROM SOR.WPP_MMOVEMENT ");
        sql.append("  WHERE CR_OPE_ID IN('1350','1800','1900','1820') AND NX_OPE_ID IN ('2100','2090') AND EVT_TYPE IN('LOGF','LFRW') ");
        sql.append(" AND FAB_DATE=TO_DATE('" + dateOfThis1 +"','YYYY-MM-DD'))F ");
        sql.append(" ON E.PNL_ID=F.PNL_ID AND E.FAB_KEY=F.FAB_KEY AND E.EVT_TIME>=F.EVT_TIME ");
        sql.append(" WHERE  N_QTY>0 GROUP BY F.EVT_USER ");
	    JdbcTemplate.query(sql.toString(), new RowCallbackHandler() {
	    	
	    	@Override
			public void processRow(ResultSet rs) throws SQLException {
	    		String emp_id = rs.getString(2);
	    		String count = rs.getString(1);
	    		String[] str = {emp_id, count};
	    		list.add(str);
	    		
	    	}
	    	
	    });
	    for(String[] str : list){
	    	PerformanceDay p = new PerformanceDay();
	    	p.setEmp_id(str[0]);
	    	p.setFab_date(DateUtil.parseDate(dateOfThis));
	    	PerformanceMon pm = hibernateInvoker.findUnique(PerformanceMon.class, "from PerformanceMon pm where pm.month = '" + dateOfThis.substring(0, 7) + "' and pm.emp_id = '" + str[0] + "' and pm.isValid = true");
	    	if(pm == null)continue;
	    	p.setOpe_fk(pm.getOpe_fk());
	    	p.setEmp_name(pm.getEmp_name());
	    	p.setFactory(pm.getFactory());
	    	p.setNg(Long.parseLong(str[1]));
	    	p.setCategory("DPPM");
	    	p.setType(34L);
	    	performanceDayManager.save(p);
	    }
	    logger.info("同步DPPM数据end");
	}

}
